"""
Writing the <cn>-locations.txt file into an SQL database table.
"""

# Importing libraries
import os, time, sys, re
import sqlite3
sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..'))

from utils.config import load_config
from utils.logger import set_logger

# Load the configuration file
config = load_config()
logger = set_logger()

# Setting the working directory to the directory of the script
os.chdir(os.path.dirname(__file__))

# CONSTANTS
START_DATETIME = config['start_datetime']  # e.g. '2020-01-01T00:00:00Z'
END_DATETIME   = config['end_datetime']    # e.g. '2022-12-31T23:59:59Z'
COUNTRY_NAME   = config['country_name']    # e.g. "mexico"
COUNTRY_CODE   = config['country_code']    # e.g. "mx"

# Convert the start and end datetimes to the format `YYYYMM`
START_YEAR_MONTH = re.sub(r'[^0-9]', '', START_DATETIME[:7])
END_YEAR_MONTH = re.sub(r'[^0-9]', '', END_DATETIME[:7])

# PATHS
PROJECT_ROOT = os.path.dirname(os.path.abspath(__file__))
INPUT_DIR     = os.path.join(PROJECT_ROOT, '../../../data', COUNTRY_NAME)
OUTPUT_DIR    = os.path.join(PROJECT_ROOT, '../../../output', COUNTRY_NAME)
LOCATIONS_TXT = os.path.join(OUTPUT_DIR, f"{COUNTRY_CODE}-query-setup", f"{START_YEAR_MONTH}_{END_YEAR_MONTH}_{COUNTRY_CODE}-locations.txt")
QUERY_DB_PATH = os.path.join(OUTPUT_DIR, f'{START_YEAR_MONTH[:4]}-{START_YEAR_MONTH[4:]}-collection/{START_YEAR_MONTH}_{END_YEAR_MONTH}_01_{COUNTRY_CODE}_query_log.db')

class QueryLogDatabase:
    def __init__(self, db_path):
        self.db_path = db_path
        # Create directory if it does not exist
        os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
        # Establish a connection to the database
        logger.info(f"Connecting to the database: {self.db_path}...")
        try:
            assert os.path.exists(self.db_path)
        except AssertionError:
            f"Database not found: {self.db_path}. Creating the database first..."
        self.conn = sqlite3.connect(db_path)
        self.c = self.conn.cursor()

    def create_table(self):
        # Create the table
        logger.info("Creating the table...")
        self.c.execute('''
        CREATE TABLE IF NOT EXISTS query (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            latitude TEXT,
            longitude TEXT,
            publishedAfter TEXT,
            publishedBefore TEXT,
            query_completed INTEGER DEFAULT 0
        )
        ''')
        self.conn.commit()
        logger.info("Table created successfully.")

    def insert_locations_from_file(self, locations_txt):
        logger.info(f"Inserting locations from {LOCATIONS_TXT}...")
        assert os.path.exists(locations_txt), f"File not found: {locations_txt}!"

        with open(locations_txt, 'r') as file:
            locations = file.readlines()
            for location in locations:
                parts = location.strip().split(',')
                if len(parts) == 4:
                    latitude, longitude, publishedAfter, publishedBefore = parts
                    self.c.execute('''
                    INSERT INTO query (latitude, longitude, publishedAfter, publishedBefore)
                    VALUES (?, ?, ?, ?)
                    ''', (latitude, longitude, publishedAfter, publishedBefore))
        self.conn.commit()
        logger.info(f"Locations inserted successfully.")

    def __del__(self):
        self.conn.close()


def main():
    if os.path.exists(QUERY_DB_PATH):
        logger.info(f"Database already exists: {QUERY_DB_PATH}")
        sys.exit(0)
    # Create the database
    db = QueryLogDatabase(QUERY_DB_PATH)
    db.create_table()
    db.insert_locations_from_file(LOCATIONS_TXT)


if __name__ == '__main__':
    start = time.time()
    main()
    end = time.time()
    logger.info(f"Execution time: {end - start:.2f} seconds")
